﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports log4net
Imports System.IO

Public Class ReportService
    Private Shared ReadOnly log As ILog = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
    Public Shared Function getPhoneFormat(ByVal sValue As String) As String
        Dim sTemp As String

        'chi lay ki tu so
        sTemp = getDigitOnly(sValue)
        If Left(sTemp, 1) = "0" Then
            getPhoneFormat = sTemp
        Else
            getPhoneFormat = Val(sTemp).ToString("(###) ###-####")
        End If
    End Function
    Public Shared Function getDigitOnly(ByVal sString As String) As String
        Dim tempString As String
        tempString = ""

        tempString = sString.Replace("(", "")
        tempString = tempString.Replace(")", "")
        tempString = tempString.Replace("-", "")
        tempString = tempString.Replace(" ", "")
        Return tempString
    End Function

    Private Shared Function getReportsHTML(ByVal sReportFile As String) As String
        Dim fs As FileStream
        Dim sr As StreamReader        
        getReportsHTML = "<html></html>"
        Try
            fs = New FileStream(sReportFile, FileMode.Open)
            sr = New StreamReader(fs)

            getReportsHTML = sr.ReadToEnd
            fs.Close()
        Catch ex As Exception
            getReportsHTML = ex.Message
        End Try
    End Function

    Public Shared Function replaceParamterWithValue(ByVal sHTMLCode As String, ByVal sParameter As String, ByVal sValue As String) As String
        replaceParamterWithValue = Replace(sHTMLCode, sParameter, sValue)
        Console.WriteLine(sHTMLCode)
    End Function

    Public Shared Function getHTMLFromDatabase(ByVal sHTMLFileName As String, ByVal maBooking As String, ByVal isKhuyenMai As Boolean, Optional loaiFile As String = "") As String
        Dim result As String = ""
        Try
            Dim htmlFile As String = getReportsHTML(sHTMLFileName)
            If loaiFile.Equals("txt") Then
                htmlFile = ProjectTools.getDataFromFile(sHTMLFileName)
            End If

            Dim sqlBooking As String = ""
            If isKhuyenMai Then
                sqlBooking = "SELECT kh.NAME as NameCustomer , kh.EMAIL , kh.PHONE1 , kh.ADDRESS AS AddressCustomer  , kh.CONGTY , kh.CHUCVU,"
                sqlBooking &= " b.MA_BOOKING, ks.NAME as NameHotel, ks.ADDRESS_1 as AddressHotel, b.TU_NGAY, b.DEN_NGAY, b.GHI_CHU, ks.STARS"
                sqlBooking &= " ,km.NAME as NameKhuyenMai, km.GIA as GiaKhuyenMai, km.NOI_DUNG, km.PHU_THU, km.LUU_Y, km.NGAY_BAT_DAU , km.NGAY_KET_THUC "
                sqlBooking &= " FROM BOOKING b,KHACH_HANG kh, KHACH_SAN ks, KHUYEN_MAI km "
                sqlBooking &= "  where b.KHACH_HANG_ID = kh.id And ks.ID = b.KHACH_SAN_ID"
                sqlBooking &= " and b.MA_BOOKING='" + maBooking + "'"
                sqlBooking &= " and kh.DISPLAY='1' and kh.DELETED='0'"
                sqlBooking &= " and b.DELETED='0'"
                sqlBooking &= " and ks.DELETED ='0' and ks.DISPLAY='1'"
                sqlBooking &= " and km.ID=b.KHUYEN_MAI_ID "
                sqlBooking &= " AND( (km.NGAY_BAT_DAU <= GETDATE() AND km.NGAY_KET_THUC >= GETDATE()) "
                sqlBooking &= " OR km.NGAY_BAT_DAU >= GETDATE()) AND km.DELETED = '0' AND km.DISPLAY = '1'"
            Else
                sqlBooking = "SELECT kh.NAME as NameCustomer , kh.EMAIL , kh.PHONE1 , kh.ADDRESS AS AddressCustomer  , kh.CONGTY , kh.CHUCVU,"
                sqlBooking &= " b.MA_BOOKING, ks.NAME as NameHotel, ks.ADDRESS_1 as AddressHotel, b.TU_NGAY, b.DEN_NGAY, b.GHI_CHU, ks.STARS"            
                sqlBooking &= " FROM BOOKING b,KHACH_HANG kh, KHACH_SAN ks "
                sqlBooking &= "  where b.KHACH_HANG_ID = kh.id And ks.ID = b.KHACH_SAN_ID"
                sqlBooking &= " and b.MA_BOOKING='" + maBooking + "'"
                sqlBooking &= " and kh.DISPLAY='1' and kh.DELETED='0'"
                sqlBooking &= " and b.DELETED='0'"
                sqlBooking &= " and ks.DELETED ='0' and ks.DISPLAY='1'"             
            End If
          
            ' ''On Error Resume Next

            Using readerBooking As SqlDataReader = Connection.ExcuteReader(sqlBooking)
                If readerBooking.HasRows Then
                    While readerBooking.Read
                        htmlFile = replaceParamterWithValue(htmlFile, "@NameCustomer@", readerBooking("NameCustomer") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@Email@", readerBooking("EMAIL") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@Phone@", readerBooking("PHONE1") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@AddressCustomer@", readerBooking("AddressCustomer") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@NameCompany@", readerBooking("CONGTY") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@ChucDanh@", readerBooking("CHUCVU") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@MaBooking@", readerBooking("MA_BOOKING") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@NameHotel@", readerBooking("NameHotel") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@AddressHotel@", readerBooking("AddressHotel") & "")
                        Dim dateFrom As Date = readerBooking("TU_NGAY")
                        Dim valueDayFrom As String = String.Format("{0}/{1}/{2}", dateFrom.Day, dateFrom.Month, dateFrom.Year)
                        Dim dateTo As Date = readerBooking("DEN_NGAY")
                        Dim valueDayTo As String = String.Format("{0}/{1}/{2}", dateTo.Day, dateTo.Month, dateTo.Year)
                        htmlFile = replaceParamterWithValue(htmlFile, "@DateFrom@", valueDayFrom & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@DateTo@", valueDayTo & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@GhiChu@", readerBooking("GHI_CHU") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@Start@", readerBooking("STARS") & "")
                        ''
                        If isKhuyenMai Then
                            htmlFile = replaceParamterWithValue(htmlFile, "@NameKhuyenMai@", readerBooking("NameKhuyenMai") & "")
                            htmlFile = replaceParamterWithValue(htmlFile, "@GiaKhuyenMai@", Format(readerBooking("GiaKhuyenMai"), "#,###") & "")
                            htmlFile = replaceParamterWithValue(htmlFile, "@DateFromKhuyenMai@", readerBooking("NGAY_BAT_DAU") & "")
                            htmlFile = replaceParamterWithValue(htmlFile, "@DateToKhuyenMai@", readerBooking("NGAY_KET_THUC") & "")
                            htmlFile = replaceParamterWithValue(htmlFile, "@NoiDunKhuyenMai@", readerBooking("NOI_DUNG") & "")
                            htmlFile = replaceParamterWithValue(htmlFile, "@PhuThu@", readerBooking("PHU_THU") & "")
                            htmlFile = replaceParamterWithValue(htmlFile, "@LuuY@", readerBooking("LUU_Y") & "")
                        End If
                    End While
                    readerBooking.Close()
                End If
            End Using
            htmlFile = replaceParamterWithValue(htmlFile, "@NameCustomer@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@Email@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@Phone@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@AddressCustomer@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@NameCompany@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@ChucDanh@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@MaBooking@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@NameHotel@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@AddressHotel@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@DateFrom@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@DateTo@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@GhiChu@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@Start@", "")

            Dim sqlDetailBooking As String = "select top 1 lp.NAME as NameLoaiPhong,  d.SO_LUONG, d.SL_GP, d.TEN_NGUOI_DAI_DIEN , "
            sqlDetailBooking &= " d.NGAY, d.DON_GIA"
            sqlDetailBooking &= " from BOOKING b, BOOKING_DETAIL d, LOAI_PHONG lp"
            sqlDetailBooking &= "  where b.id = d.BOOKING_ID"
            sqlDetailBooking &= " and lp.id=d.LOAI_PHONG_ID"
            sqlDetailBooking &= " and b.MA_BOOKING='" + maBooking + "'"


            Dim totalSoTien As Double = 0
            Using readerBookingDetail As SqlDataReader = Connection.ExcuteReader(sqlDetailBooking)
                If readerBookingDetail.HasRows Then
                    While readerBookingDetail.Read
                        htmlFile = replaceParamterWithValue(htmlFile, "@NameLoaiPhong@", readerBookingDetail("NameLoaiPhong") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@SoLuongPhong@", readerBookingDetail("SO_LUONG") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@SoGiuongPhu@", readerBookingDetail("SL_GP") & "")
                        Dim ngay As Date = readerBookingDetail("NGAY")
                        Dim valueDay As String = String.Format("{0}/{1}/{2}", ngay.Day, ngay.Month, ngay.Year)
                        htmlFile = replaceParamterWithValue(htmlFile, "@NgayNhanPhong@", valueDay & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@GiaLoaiPhong@", Format(readerBookingDetail("DON_GIA"), "#,###") & "")
                        htmlFile = replaceParamterWithValue(htmlFile, "@NguoiDaiDien@", readerBookingDetail("TEN_NGUOI_DAI_DIEN") & "")
                        totalSoTien = readerBookingDetail("DON_GIA") * readerBookingDetail("SO_LUONG")
                    End While
                    readerBookingDetail.Close()
                    htmlFile = replaceParamterWithValue(htmlFile, "@TotalSoTien@", Format(totalSoTien, "#,###"))
                End If
            End Using
            htmlFile = replaceParamterWithValue(htmlFile, "@NameLoaiPhong@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@SoLuongPhong@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@SoGiuongPhu@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@NgayNhanPhong@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@GiaLoaiPhong@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@NguoiDaiDien@", "")
            htmlFile = replaceParamterWithValue(htmlFile, "@TotalSoTien@", "")

            result = htmlFile
            If result = "" Then
                result = "<h1>Không load được Mã booking # " & maBooking & "</h1>"
            End If
            Connection.Close()

        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return result
    End Function

End Class
